import mysql from 'mysql'

class Database {
  constructor(config) {
    this.pool = mysql.createPool({
      host: config.host || 'localhost',
      port: config.port || 3306,
      user: config.user || '',
      password: config.password || '',
      database: config.database,
      connectionLimit: config.connectionLimit || 10,
      waitForConnections: true,
      queueLimit: 0
    });
  }

  async query(sql, params = []) {
    return new Promise((resolve, reject) => {
      this.pool.query(sql, params, (error, results, fields) => {
        if (error) {
          reject(error);
        } else {
          resolve({ results, fields });
        }
      });
    });
  }

  async close() {
    return new Promise((resolve, reject) => {
      this.pool.end(err => {
        if (err) {
          reject(err);
        } else {
          resolve();
        }
      });
    });
  }
}

class Model {
  constructor(db, tableName, schema) {
    this.db = db;
    this.tableName = tableName;
    this.schema = schema;
    this.initialized = false;
  }

  async initialize() {
    if (this.initialized) return;
    
    const columns = Object.entries(this.schema)
      .map(([name, type]) => {
        const typeMapping = {
          Number: 'INT',
          String: 'VARCHAR(255)',
          Date: 'TIMESTAMP',
          Boolean: 'TINYINT(1)'
        };
        return `${name} ${typeMapping[type.name] || 'VARCHAR(255)'}`;
      })
      .join(', ');
    
    const createTableSQL = `
      CREATE TABLE IF NOT EXISTS ${this.tableName} (
        id INT PRIMARY KEY AUTO_INCREMENT,
        ${columns}
      )`;
    
    await this.db.query(createTableSQL);
    this.initialized = true;
  }

  async find(conditions = {}, fields = ['*']) {
    await this.initialize();
    const whereClause = Object.keys(conditions).length > 0 
      ? `WHERE ${Object.entries(conditions).map(([key, val]) => `${key} = ?`).join(' AND ')}`
      : '';
    const sql = `SELECT ${fields.join(', ')} FROM ${this.tableName} ${whereClause}`;
    const params = Object.values(conditions);
    const { results } = await this.db.query(sql, params);
    return results;
  }

  async findOne(conditions, fields = ['*']) {
    await this.initialize(); // 确保表已初始化
    const whereClause = Object.keys(conditions).length > 0
      ? `WHERE ${Object.entries(conditions).map(([key, val]) => `${key} = ?`).join(' AND ')}`
      : '';
    // 直接在 SQL 查询中添加 LIMIT 1
    const sql = `SELECT ${fields.join(', ')} FROM ${this.tableName} ${whereClause} LIMIT 1`;
    const params = Object.values(conditions);
    const { results } = await this.db.query(sql, params);
    return results[0] || null;
  }

  async insert(data) {
    await this.initialize();
    const keys = Object.keys(data);
    const values = Object.values(data);
    const placeholders = keys.map(() => '?').join(', ');
    const sql = `INSERT INTO ${this.tableName} (${keys.join(', ')}) VALUES (${placeholders})`;
    const { results } = await this.db.query(sql, values);
    return results.insertId;
  }

  async update(conditions, updates) {
    await this.initialize();
    const setClause = Object.keys(updates).map(key => `${key} = ?`).join(', ');
    const whereClause = Object.keys(conditions).map(key => `${key} = ?`).join(' AND ');
    const sql = `UPDATE ${this.tableName} SET ${setClause} WHERE ${whereClause}`;
    const params = [...Object.values(updates), ...Object.values(conditions)];
    const { results } = await this.db.query(sql, params);
    return results.affectedRows;
  }

  async delete(conditions) {
    await this.initialize();
    const whereClause = Object.keys(conditions).map(key => `${key} = ?`).join(' AND ');
    const sql = `DELETE FROM ${this.tableName} WHERE ${whereClause}`;
    const params = Object.values(conditions);
    const { results } = await this.db.query(sql, params);
    return results.affectedRows;
  }

  async paginate(page = 1, pageSize = 10, conditions = {}) {
    await this.initialize();
    const offset = (page - 1) * pageSize;
    const whereClause = Object.keys(conditions).length > 0 
      ? `WHERE ${Object.entries(conditions).map(([key, val]) => `${key} = ?`).join(' AND ')}`
      : '';
    const countSql = `SELECT COUNT(*) as total FROM ${this.tableName} ${whereClause}`;
    const dataSql = `SELECT * FROM ${this.tableName} ${whereClause} LIMIT ?, ?`;
    const params = [...Object.values(conditions), offset, pageSize];
    
    const [{ results: countResults }, { results: dataResults }] = await Promise.all([
      this.db.query(countSql, Object.values(conditions)),
      this.db.query(dataSql, params)
    ]);
    
    return {
      data: dataResults,
      total: countResults[0].total,
      page,
      pageSize,
      totalPages: Math.ceil(countResults[0].total / pageSize)
    };
  }
}

class ORM {
  constructor(config) {
    this.db = new Database(config);
    this.models = {};
  }

  define(modelName, schema) {
    if (this.models[modelName]) {
      return this.models[modelName];
    }
    const model = new Model(this.db, modelName, schema);
    this.models[modelName] = model;
    return model;
  }

  async close() {
    await this.db.close();
  }
}

// 数据库配置 - 推荐使用环境变量
// 您可以在项目根目录创建 .env 文件并使用 dotenv 库加载环境变量
// 例如: DB_HOST=127.0.0.1
const dbConfig = {
  host: process.env.DB_HOST || '127.0.0.1',
  port: parseInt(process.env.DB_PORT) || 3306,
  user: process.env.DB_USER || 'root', // 如果环境变量未设置，则使用默认值
  password: process.env.DB_PASSWORD || '123456', // 如果环境变量未设置，则使用默认值
  database: process.env.DB_DATABASE || 'book', // 如果环境变量未设置，则使用默认值
  connectionLimit: parseInt(process.env.DB_CONNECTION_LIMIT) || 10,
  waitForConnections: true,
  queueLimit: 0
};

// 创建并导出 ORM 实例
export const orm = new ORM(dbConfig);

// 可选: 如果您希望集中定义和导出模型，可以这样做：
export const user = orm.define('users', {
  username: String,
  password: String,
  age: Number,
  email: String
});

// 可选: 如果您仍需要一个类似于之前直接访问 db 连接池的导出 (不推荐，ORM 提供了更高级的抽象)
// export const db = orm.db; // 这将导出 Database 类的实例
